package cn.buk.tms.dao;

import cn.buk.common.sc.CommonSearchCriteria;
import cn.buk.common.sc.Page;
import cn.buk.tms.dto.PnrSummary;
import cn.buk.common.util.DateUtil;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import jakarta.persistence.Query;
import jakarta.persistence.Tuple;
import jakarta.persistence.criteria.*;
import jakarta.transaction.Transactional;

import cn.buk.tms.entity.misc.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.BeanUtils;
import org.springframework.stereotype.Component;

@Component
@Transactional
public class PnrDaoImpl extends AbstractDao implements PnrDao {

    private static final Logger logger = LoggerFactory.getLogger(PnrDaoImpl.class);

    @Override
    public int createOrUpdate(final int enterpriseId, Pnr newPnr) {
        if (newPnr == null) {
            throw new IllegalArgumentException("pnr cannot be null");
        }

        newPnr.setEnterpriseId(enterpriseId);

        List<Pnr> pnrs = em.createQuery("select o from Pnr o " +
                "where o.enterpriseId = :enterpriseId and o.pnrNo = :pnrNo", Pnr.class)
                .setParameter("enterpriseId", enterpriseId)
                .setParameter("pnrNo", newPnr.getPnrNo())
                .getResultList();
        if (pnrs.isEmpty()) {
            em.persist(newPnr);
        } else {
            Pnr oldPnr = pnrs.get(0);

            //保存之前的PNR内容到历史库
            PnrUsed pnrUsed = new PnrUsed();
            BeanUtils.copyProperties(oldPnr, pnrUsed);
            pnrUsed.setId(0);
            pnrUsed.setOldId(oldPnr.getId());
            pnrUsed.setEnterpriseId(enterpriseId);
            em.persist(pnrUsed);

            BeanUtils.copyProperties(newPnr, oldPnr, "id");
            oldPnr.setLastUpdate(DateUtil.getCurDateTime());
            em.merge(oldPnr);

            newPnr.setId(oldPnr.getId());
        }

        return newPnr.getId();
    }

    @Override
    public List<Pnr> search(final int enterpriseId, CommonSearchCriteria sc) {
        List<Pnr> results = null;

        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<Pnr> cq = cb.createQuery(Pnr.class);
        Root<Pnr> root = cq.from(Pnr.class);
        root.alias("o");

        var predicates0 = getPredicates_searchPnr(enterpriseId, sc, cb, root);

        // count items
        CriteriaQuery<Long> countCq = cb.createQuery(Long.class);
        Root<Pnr> countRoot = countCq.from(Pnr.class);
        countRoot.alias("o");

        var predicates1 = getPredicates_searchPnr(enterpriseId, sc, cb, countRoot);

        countCq.select(cb.count(countRoot)).where(predicates1.toArray(new Predicate[predicates1.size()]));
        Long count = em.createQuery(countCq).getSingleResult();


        if (count > 0) {
            sc.getPage().setRowCount(count.intValue());

            //search block
            int firstPosition = (sc.getPage().getPageNo() - 1) * sc.getPage().getPageSize();
            cq.select(root).where(predicates0.toArray(new Predicate[predicates0.size()]));

            List<Order> orderByes = new ArrayList<>();

            if (sc.getOrderBy() == 1) {
                orderByes.add(cb.desc(root.get("psgCount")));
            } else if (sc.getOrderBy() == 2) {
                orderByes.add(cb.desc(root.get("etermUsername")));
            } else if (sc.getOrderBy() == 3) {
                orderByes.add(cb.desc(root.get("segStatus")));
            } else if (sc.getOrderBy() == 4) {
                orderByes.add(cb.desc(root.get("lastUpdate")));
            }

            orderByes.add(cb.desc(root.get("id")));

            cq.orderBy(orderByes);

            sc.getPage().setRowCount(count.intValue());

            results = em.createQuery(cq)
                    .setFirstResult(firstPosition)
                    .setMaxResults(sc.getPageSize()).getResultList();

        }

        if (results == null) {
            results = new ArrayList<>();
        }
        return results;
    }

    private static List<Predicate> getPredicates_searchPnr(int enterpriseId, CommonSearchCriteria sc, CriteriaBuilder cb, Root<Pnr> root) {
        List<Predicate> predicates = new ArrayList<>();

        Predicate predicate = cb.conjunction();
        predicates.add(predicate);

        predicate = cb.equal(root.get(Pnr_.enterpriseId), enterpriseId);
        predicates.add(predicate);


        if (sc.getPnrNo().length() > 0) {
            predicate = cb.like(root.get(Pnr_.pnrNo), "%" + sc.getPnrNo() + "%");
            predicates.add(predicate);
        }
        if (sc.getEtermUsername() != null && sc.getEtermUsername().length() > 0) {
            predicate = cb.equal(root.get(Pnr_.etermUsername), sc.getEtermUsername());
            predicates.add(predicate);
        }
        if (sc.getSegStatus() != null && sc.getSegStatus().length() == 2) {
            predicate = cb.equal(root.get(Pnr_.segStatus), sc.getSegStatus());
            predicates.add(predicate);
        }

        if (sc.getDport().length() == 3) {
            predicate = cb.equal(root.get(Pnr_.dport), sc.getDport());
            predicates.add(predicate);
        }
        if (sc.getAport().length() == 3) {
            predicate = cb.equal(root.get(Pnr_.aport), sc.getAport());
            predicates.add(predicate);
        }

        if (sc.getBeginDate() != null) {
            predicate = cb.greaterThanOrEqualTo(root.get(Pnr_.createTime), sc.getBeginDate());
            predicates.add(predicate);
        }

        if (sc.getEndDate() != null) {
            predicate = cb.lessThan(root.get(Pnr_.createTime), sc.getEndDate());
            predicates.add(predicate);
        }

        if (sc.getCarrier() != null && sc.getCarrier().trim().length() == 2) {
            predicate = cb.like(root.get(Pnr_.flightNo), sc.getCarrier() + "%");
            predicates.add(predicate);
        }

        if (sc.getDzStatus() >= 0) {
            predicate = cb.equal(root.get(Pnr_.dzStatus), sc.getDzStatus());
            predicates.add(predicate);
        }
        return predicates;
    }

    @Override
    public List<Pnr> search(final int enterpriseId, final int maxResults) {
        return em.createQuery("select o from Pnr o where o.enterpriseId = :enterpriseId " +
                "and o.segStatus is null and (o.checkTime is null or o.checkTime < :checkTime) " +
                "order by o.id desc", Pnr.class)
                .setParameter("enterpriseId", enterpriseId)
                .setParameter("checkTime", DateUtil.getYesterday())
                .setMaxResults(maxResults)
                .getResultList();
    }

    @Override
    public List<Pnr> search(int enterpriseId, String segStatus, int segCount) {
        return em.createQuery("select o from Pnr o where o.enterpriseId = :enterpriseId and o.segStatus = :segStatus and o.segCount = :segCount order by o.id desc", Pnr.class)
                .setParameter("enterpriseId", enterpriseId)
                .setParameter("segStatus", segStatus)
                .setParameter("segCount", segCount)
                .setMaxResults(5000)
                .getResultList();
    }

    /**
     * 查找pnr的checktime时间超过24小时的
     *
     * @param enterpriseId
     * @param max
     * @return
     */
    @Override
    public List<Pnr> searchCheckTimeMoreThan24Hours(int enterpriseId, int max) {

        Date checkTime = DateUtil.addDays(DateUtil.getCurDateTime(), -1);
        logger.info(DateUtil.formatDate(checkTime));
        return em.createQuery("select o from Pnr o where o.enterpriseId = :enterpriseId " +
                "and  (o.checkTime is null or o.checkTime < :checkTime) " +
                "order by o.id desc", Pnr.class)
                .setParameter("enterpriseId", enterpriseId)
                .setParameter("checkTime", checkTime)
                .setMaxResults(max)
                .getResultList();

    }


    @SuppressWarnings("unchecked")
    @Override
    public Pnr getPnrByPnrNo(String pnrNo, int enterpriseId) {


        String sql = "select o from Pnr o where o.pnrNo = :pnrNo and o.enterpriseId = :enterpriseId";
        Query query = em.createQuery(sql)
                .setParameter("pnrNo", pnrNo)
                .setParameter("enterpriseId", enterpriseId);

        List<Pnr> pnrs = query.getResultList();

        return pnrs.size() == 0 ? null : pnrs.get(0);

    }

    @Override
    public int delete(int id, PnrLog log) {
        int returnCode = 0;


        returnCode = em.createQuery("delete from Pnr o where o.id = :id")
                .setParameter("id", id)
                .executeUpdate();

        if (log != null) {
            em.persist(log);
        }


        return returnCode;
    }

    @Override
    public int getPnrStatusCount(String pnrStatus, String etermUsername, int enterpriseId) {

        Long count = (Long) em.createQuery("select count(o) from Pnr o where o.enterpriseId = :enterpriseId" +
                " and o.segStatus = :segStatus" +
                " and o.issueDate = :issueDate" +
                " and o.etermUsername = :etermUsername" +
                " and (o.checkDone is null or o.checkDone = '0')")
                .setParameter("enterpriseId", enterpriseId)
                .setParameter("segStatus", pnrStatus)
                .setParameter("issueDate", DateUtil.getCurDate())
                .setParameter("etermUsername", etermUsername)
                .getSingleResult();

        return count.intValue();

    }

    @Override
    public int delete(int id, int enterpriseId, int userId) {
        int returnCode = 0;

        Pnr pnr = em.find(Pnr.class, id);
        if (pnr == null) {
            return returnCode;
        }


        returnCode = em.createQuery("delete from Pnr o where o.enterpriseId = :enterpriseId and o.id = :id")
                .setParameter("enterpriseId", enterpriseId)
                .setParameter("id", id)
                .executeUpdate();

        PnrLog pnrLog = new PnrLog();
        pnrLog.setEnterpriseId(enterpriseId);
        pnrLog.setPnrNo(pnr.getPnrNo());
        pnrLog.setRemark("被用户(" + userId + ")删除.");
        em.persist(pnrLog);


        returnCode = 1;


        return returnCode;
    }

    @Override
    public List<PnrLog> searchPnrLog(final int enterpriseId, CommonSearchCriteria sc) {
        List<PnrLog> results = null;


        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<PnrLog> cq = cb.createQuery(PnrLog.class);
        Root<PnrLog> root = cq.from(PnrLog.class);
        root.alias("o");

        List<Predicate> predicates = new ArrayList<>();

        Predicate predicate = cb.conjunction();
        predicates.add(predicate);

        predicate = cb.equal(root.get(PnrLog_.enterpriseId), enterpriseId);
        predicates.add(predicate);

//        if (sc.getPnrNo().length() > 0) {
//            where = cb.and(where, cb.like(root.get(PnrLog_.pnrNo), sc.getPnrNo() + "%"));
//        }
//
//        if (sc.getRemark().length() > 0) {
//            where = cb.and(where, cb.like(root.get(PnrLog_.remark), "%" + sc.getRemark() + "%"));
//        }

        // count items
        CriteriaQuery<Long> countCq = cb.createQuery(Long.class);
        Root<PnrLog> countRoot = countCq.from(PnrLog.class);
        countRoot.alias("o");

        countCq.select(cb.count(countRoot)).where(predicates.toArray(new Predicate[predicates.size()]));
        Long count = em.createQuery(countCq).getSingleResult();


        if (count > 0) {
            sc.getPage().setRowCount(count.intValue());

            //search block
            int firstPosition = (sc.getPage().getPageNo() - 1) * sc.getPage().getPageSize();
            cq.select(root).where(predicates.toArray(new Predicate[predicates.size()]));

            List<Order> orderByes = new ArrayList<>();

            orderByes.add(cb.desc(root.get("id")));

            cq.orderBy(orderByes);

            sc.getPage().setRowCount(count.intValue());

            results = em.createQuery(cq)
                    .setFirstResult(firstPosition)
                    .setMaxResults(sc.getPageSize()).getResultList();

        }

        if (results == null) {
            results = new ArrayList<>();
        }
        return results;
    }

    @Override
    public List<Pnr> searchPnr(final int enterpriseId, final Date departureDate, final String flightNo, final String subClass) {
        List<Pnr> results = null;


        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<Pnr> cq = cb.createQuery(Pnr.class);
        Root<Pnr> root = cq.from(Pnr.class);

        Predicate where = cb.conjunction();
        where = cb.and(where, cb.equal(root.get(Pnr_.enterpriseId), enterpriseId));
        where = cb.and(where, cb.equal(root.get(Pnr_.ddate), departureDate));
        where = cb.and(where, cb.equal(root.get(Pnr_.flightNo), flightNo));
        where = cb.and(where, cb.equal(root.get(Pnr_.subclass), subClass));
        cq.where(where);

        List<Order> orderByes = new ArrayList<>();
        orderByes.add(cb.asc(root.get("segStatus")));
        cq.orderBy(orderByes);

        results = em.createQuery(cq).getResultList();


        return results == null ? new ArrayList<Pnr>() : results;
    }

    /**
     * 删除无效编码
     *
     * @param enterpriseId
     * @param departureDate
     * @param flightNo
     * @param subClass
     * @return
     */
    @Override
    public int deleteInvalidPnr(final int enterpriseId, final Date departureDate, final String flightNo, final String subClass, final String pnrNo, final int userId) {
        int retCode = 0;


        retCode = em.createQuery("delete from Pnr o where o.enterpriseId = :enterpriseId" +
                " and o.ddate = :ddate" +
                " and o.flightNo = :flightNo" +
                " and o.subclass = :subClass" +
                " and (o.segStatus = :segStatus1 or o.segStatus = :segStatus2)")
                .setParameter("enterpriseId", enterpriseId)
                .setParameter("ddate", departureDate)
                .setParameter("flightNo", flightNo)
                .setParameter("subClass", subClass)
                .setParameter("segStatus1", "HX")
                .setParameter("segStatus2", "NO")
                .executeUpdate();

        PnrLog pnrLog = new PnrLog();
        pnrLog.setEnterpriseId(enterpriseId);
        pnrLog.setPnrNo(pnrNo);
        pnrLog.setRemark(flightNo + "," + subClass + "共 " + retCode + " 个编码被用户(" + userId + ")删除.");
        em.persist(pnrLog);


        return retCode;
    }

    @Override
    public int savePnrLog(int enterpriseId, String pnrNo, String pnrDetail, String remark) {
        PnrLog pnrLog = new PnrLog();
        pnrLog.setEnterpriseId(enterpriseId);
        pnrLog.setPnrNo(pnrNo);
        pnrLog.setPnrDetail(pnrDetail);
        pnrLog.setRemark(remark);

        em.persist(pnrLog);

        return pnrLog.getId();
    }

    @Override
    public List<FareCondition> searchFareConditions(int enterpriseId, CommonSearchCriteria sc) {
        List<FareCondition> results = null;

        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<FareCondition> cq = cb.createQuery(FareCondition.class);
        Root<FareCondition> root = cq.from(FareCondition.class);

        Predicate where = cb.conjunction();

        if (enterpriseId != Enterprise.ENTERPRISE_PLATFORM_ADMINISTRATOR) {
            where = cb.and(where, cb.equal(root.get(FareCondition_.enterpriseId), enterpriseId));
        } else {
            where = cb.and(where, cb.equal(root.get(FareCondition_.enterpriseId), 0));
        }

        if (sc.getCarrier().length() == 2) {
            where = cb.and(where, cb.like(root.get(FareCondition_.carrier), sc.getCarrier()));
        }

        if (sc.getSubClass().length() == 1) {
            where = cb.and(where, cb.like(root.get(FareCondition_.subclass), sc.getSubClass()));
        }


        cq.where(where);
        List<Order> orderByes = new ArrayList<>();
        orderByes.add(cb.desc(root.get("id")));

        cq.orderBy(orderByes);

        //计算根据条件查询得出的数据总数
        CriteriaQuery<Long> countQuery = cb.createQuery(Long.class);
        Root<FareCondition> countRoot = countQuery.from(FareCondition.class);

        Predicate where0 = cb.conjunction();
        //****
        if (enterpriseId != Enterprise.ENTERPRISE_PLATFORM_ADMINISTRATOR) {
            where0 = cb.and(where0, cb.equal(countRoot.get(FareCondition_.enterpriseId), enterpriseId));
        } else {
            where0 = cb.and(where0, cb.equal(countRoot.get(FareCondition_.enterpriseId), 0));
        }

        if (sc.getCarrier().length() == 2) {
            where0 = cb.and(where0, cb.like(countRoot.get(FareCondition_.carrier), sc.getCarrier()));
        }

        if (sc.getSubClass().length() == 1) {
            where0 = cb.and(where0, cb.like(countRoot.get(FareCondition_.subclass), sc.getSubClass()));
        }


        countQuery.select(cb.count(countRoot)).where(where0);
        Long count = em.createQuery(countQuery).getSingleResult();

        int maxResults = count.intValue();
        if (maxResults > 0) {
            Page page = sc.getPage();
            page.setRowCount(maxResults);
            int i = (page.getPageNo() - 1) * page.getPageSize();

            if (i < 0) {
                i = 0;
            }

            try {
                results = em.createQuery(cq)
                        .setFirstResult(i)
                        .setMaxResults(page.getPageSize()).getResultList();

            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }


        if (results == null) {
            results = new ArrayList<>();
        }

        return results;
    }


    @Override
    public int create(PnrAlert alert) {
        int returnCode = 0;
        em.persist(alert);

        returnCode = alert.getId();


        return returnCode;
    }

    @Override
    public List<PnrAlert> searchPnrAlert(int enterpriseId, int alertType, CommonSearchCriteria sc) {
        List<PnrAlert> results = null;


        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<PnrAlert> cq = cb.createQuery(PnrAlert.class);
        Root<PnrAlert> root = cq.from(PnrAlert.class);

        Predicate where = cb.conjunction();
        where = cb.and(where, cb.equal(root.get(PnrAlert_.enterpriseId), enterpriseId));

        if (alertType > 0) {
            where = cb.and(where, cb.equal(root.get(PnrAlert_.alertType), alertType));
        }
        if (sc.getPnrNo().length() == 6) {
            where = cb.and(where, cb.equal(root.get(PnrAlert_.pnrNo), sc.getPnrNo()));
        }

        cq.where(where);

        List<Order> orderByes = new ArrayList<>();
        orderByes.add(cb.desc(root.get("id")));
        cq.orderBy(orderByes);


        // count items
        CriteriaQuery<Long> countCq = cb.createQuery(Long.class);
        Root<PnrAlert> countRoot = countCq.from(PnrAlert.class);

        Predicate where0 = cb.conjunction();
        where0 = cb.and(where0, cb.equal(countRoot.get(PnrAlert_.enterpriseId), enterpriseId));

        if (alertType > 0) {
            where0 = cb.and(where0, cb.equal(countRoot.get(PnrAlert_.alertType), alertType));
        }

        if (sc.getPnrNo().length() == 6) {
            where0 = cb.and(where0, cb.equal(countRoot.get(PnrAlert_.pnrNo), sc.getPnrNo()));
        }

        countCq.select(cb.count(countRoot)).where(where0);
        Long count = em.createQuery(countCq).getSingleResult();

        int maxResults = count.intValue();
        if (maxResults > 0) {

            sc.getPage().setRowCount(maxResults);
            int i = (sc.getPageNo() - 1) * sc.getPageSize();

            if (i < 0) {
                i = 0;
            }

            try {
                results = em.createQuery(cq)
                        .setFirstResult(i)
                        .setMaxResults(sc.getPageSize()).getResultList();

            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }


        return results == null ? new ArrayList<PnrAlert>() : results;
    }


    @Override
    public List<Pnr> searchPnrXX() {

        List<Pnr> results = em.createQuery("select o from Pnr o " +
                "where o.segStatus = :segStatus order by o.id desc", Pnr.class)
                .setParameter("segStatus", "XX")
                .setMaxResults(10000)
                .getResultList();

        for (Pnr pnr : results) {
            pnr.getPnrDetail();
        }

        return results;

    }

    @Override
    public List<Pnr> searchPnrOtherOffice(final String officeNo) {

        List<Pnr> results = em.createQuery("select o from Pnr o " +
                "where o.bookOfficeNo is not null and o.bookOfficeNo <> :officeNo  order by o.id desc", Pnr.class)
                .setParameter("officeNo", officeNo)
                .setMaxResults(10000)
                .getResultList();

        for (Pnr pnr : results) {
            pnr.getPnrDetail();
        }

        return results;

    }

    @Override
    public List<PnrUsed> searchPnrUsed(int enterpriseId, CommonSearchCriteria sc) {
        List<PnrUsed> results = null;


        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<PnrUsed> cq = cb.createQuery(PnrUsed.class);
        Root<PnrUsed> root = cq.from(PnrUsed.class);
        root.alias("o");

        List<Predicate> predicates = new ArrayList<>();

        Predicate predicate = cb.conjunction();
        predicates.add(predicate);

        predicate = cb.equal(root.get(PnrUsed_.enterpriseId), enterpriseId);
        predicates.add(predicate);

        if (sc.getPnrNo() != null) {
            final String pnrNo = sc.getPnrNo().trim();
            if (pnrNo.length() == 6) {
                predicate = cb.equal(root.get(PnrUsed_.pnrNo), pnrNo);
                predicates.add(predicate);
            } else if (pnrNo.length() > 0) {
                predicate = cb.like(root.get(PnrUsed_.pnrNo), "%" + pnrNo + "%");
                predicates.add(predicate);
            }
        }

        if (sc.getEtermUsername() != null && sc.getEtermUsername().length() > 0) {
            predicate = cb.equal(root.get(PnrUsed_.etermUsername), sc.getEtermUsername());
            predicates.add(predicate);
        }
        if (sc.getSegStatus() != null && sc.getSegStatus().length() == 2) {
            predicate = cb.equal(root.get(PnrUsed_.segStatus), sc.getSegStatus());
            predicates.add(predicate);
        }

        if (sc.getDport().length() == 3) {
            predicate = cb.equal(root.get(PnrUsed_.dport), sc.getDport());
            predicates.add(predicate);
        }
        if (sc.getAport().length() == 3) {
            predicate = cb.equal(root.get(PnrUsed_.aport), sc.getAport());
            predicates.add(predicate);
        }

        if (sc.getBeginDate() != null) {
            predicate = cb.greaterThanOrEqualTo(root.get(PnrUsed_.createTime), sc.getBeginDate());
            predicates.add(predicate);
        }

        if (sc.getEndDate() != null) {
            predicate = cb.lessThan(root.get(PnrUsed_.createTime), sc.getEndDate());
            predicates.add(predicate);
        }

        if (sc.getCarrier() != null && sc.getCarrier().trim().length() == 2) {
            predicate = cb.like(root.get(PnrUsed_.flightNo), sc.getCarrier() + "%");
            predicates.add(predicate);
        }

        if (sc.getDzStatus() >= 0) {
            predicate = cb.equal(root.get(PnrUsed_.dzStatus), sc.getDzStatus());
            predicates.add(predicate);
        }

        ////////////////////////
        // count items
        CriteriaQuery<Long> countCq = cb.createQuery(Long.class);
        Root<PnrUsed> countRoot = countCq.from(PnrUsed.class);
        countRoot.alias("o");

        countCq.select(cb.count(countRoot)).where(predicates.toArray(new Predicate[predicates.size()]));
        Long count = em.createQuery(countCq).getSingleResult();


        if (count > 0) {
            sc.getPage().setRowCount(count.intValue());

            //search block
            int firstPosition = (sc.getPage().getPageNo() - 1) * sc.getPage().getPageSize();
            cq.select(root).where(predicates.toArray(new Predicate[predicates.size()]));

            List<Order> orderByes = new ArrayList<>();

            if (sc.getOrderBy() == 1) {
                orderByes.add(cb.desc(root.get("psgCount")));
            } else if (sc.getOrderBy() == 2) {
                orderByes.add(cb.desc(root.get("etermUsername")));
            } else if (sc.getOrderBy() == 3) {
                orderByes.add(cb.desc(root.get("segStatus")));
            }

            orderByes.add(cb.desc(root.get("id")));

            cq.orderBy(orderByes);

            sc.getPage().setRowCount(count.intValue());
            try {
                results = em.createQuery(cq)
                        .setFirstResult(firstPosition)
                        .setMaxResults(sc.getPageSize()).getResultList();

            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }


        if (results == null) {
            results = new ArrayList<>();
        }
        return results;
    }

    @Override
    public int savePnrMonitor(int enterpriseId, String pnrNo, int monitorType) {
        int retCode;


        retCode = em.createQuery("update Pnr o set o.monitoring = :monitoring where o.enterpriseId = :enterpriseId and o.pnrNo = :pnrNo")
                .setParameter("enterpriseId", enterpriseId)
                .setParameter("pnrNo", pnrNo)
                .setParameter("monitoring", monitorType)
                .executeUpdate();

        if (retCode == 0) {
            Pnr pnr = new Pnr();
            pnr.setEnterpriseId(enterpriseId);
            pnr.setPnrNo(pnrNo);
            pnr.setMonitoring(monitorType);
            em.persist(pnr);
            retCode = pnr.getId();
        }


        return retCode;
    }

    /**
     * 查找用于CTCM/CTCM项目的手机号
     *
     * @param enterpriseId
     * @param mobileUsage  用途
     * @return
     */
    @Override
    public String applyCtcmMobile(final int enterpriseId, Integer mobileUsage) {
        String retMobile = "";

        List<PnrMobile> list;
        if (mobileUsage == null || mobileUsage == 0) {
            list = em.createQuery("select o from PnrMobile o where o.enterpriseId = :enterpriseId order by o.count", PnrMobile.class)
                    .setParameter("enterpriseId", enterpriseId)
                    .setMaxResults(1)
                    .getResultList();
        } else {
            list = em.createQuery("select o from PnrMobile o where o.enterpriseId = :enterpriseId and o.usage = :usage order by o.count", PnrMobile.class)
                    .setParameter("enterpriseId", enterpriseId)
                    .setParameter("usage", mobileUsage)
                    .setMaxResults(1)
                    .getResultList();
        }

        if (list.size() > 0) {
            PnrMobile pnrMobile = list.get(0);
            retMobile = pnrMobile.getMobile();

            pnrMobile.setCount(pnrMobile.getCount() + 1);
            em.merge(pnrMobile);
        }

        return retMobile;
    }

    /**
     * 查找旅行代码
     *
     * @param enterpriseId
     * @param carrier
     * @return
     */
    @Override
    public PnrTourCode searchTourCode(int enterpriseId, String carrier) {
        List<PnrTourCode> list = em.createQuery("select o from PnrTourCode o " +
                "where o.enterpriseId = :enterpriseId " +
                "and o.carrier = :carrier " +
                "and o.status = :status ", PnrTourCode.class)
                .setParameter("enterpriseId", enterpriseId)
                .setParameter("carrier", carrier)
                .setParameter("status", 1)
                .setMaxResults(1)
                .getResultList();

        return list.size() > 0 ? list.get(0) : null;
    }

    @Override
    public List<PnrSummary> statPnrByUsername(int enterpriseId, CommonSearchCriteria sc) {
        final Date startTicket = DateUtil.getCurDateTime();

        List<PnrSummary> results = null;


        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<Tuple> cq = cb.createQuery(Tuple.class);

        Root<Pnr> root = cq.from(Pnr.class);
        root.alias("o");
        //cq.select(root);
        // cb.tuple()
        cq.select(cb.tuple(root.get(Pnr_.etermUsername), cb.count(root)));
        cq.groupBy(root.get(Pnr_.etermUsername));

        Predicate where = cb.conjunction();
        where = cb.and(where, cb.equal(root.get(Pnr_.enterpriseId), enterpriseId));

        if (sc.getPnrNo().length() > 0) {
            where = cb.and(where, cb.like(root.get(Pnr_.pnrNo), "%" + sc.getPnrNo() + "%"));
        }

        if (sc.getDzStatus() > -1) {
            where = cb.and(where, cb.equal(root.get(Pnr_.dzStatus), sc.getDzStatus()));
        }
        if (sc.getSegStatus() != null && sc.getSegStatus().length() == 2) {
            where = cb.and(where, cb.equal(root.get(Pnr_.segStatus), sc.getSegStatus()));
        }
        //查询etermUsername条件
        if (sc.getEtermUsername() != null && sc.getEtermUsername().length() > 0) {
            where = cb.and(where, cb.equal(root.get(Pnr_.etermUsername), sc.getEtermUsername()));
        }
        if (sc.getTodayMode() == 1) {
            where = cb.and(where, cb.equal(root.get(Pnr_.issueDate), DateUtil.getCurDate()));
            Predicate p1 = cb.equal(root.get(Pnr_.checkDone), "0");
            Predicate p2 = cb.isNull(root.get(Pnr_.checkDone));
            where = cb.and(where, cb.and(cb.or(p1, p2)));
        }

        if (sc.getXeStatus().compareToIgnoreCase("-1") != 0) {
            where = cb.and(where, cb.equal(root.get(Pnr_.xeStatus), sc.getXeStatus()));
        }

        if (sc.getDport().length() == 3) {
            where = cb.and(where, cb.equal(root.get(Pnr_.dport), sc.getDport()));
        }
        if (sc.getAport().length() == 3) {
            where = cb.and(where, cb.equal(root.get(Pnr_.aport), sc.getAport()));
        }
//            if (sc.getFlightNo().length() >= 2) {
//                where = cb.and(where, cb.like(root.get(Pnr_.flightNo), sc.getFlightNo() + "%"));
//            }

//            System.out.println(sc.getBeginDate());
//            System.out.println(sc.getEndDate());
        if (sc.getBeginDate() != null && sc.getEndDate() != null) {
            Date endDate1 = DateUtil.addDays(sc.getEndDate(), 1);
            where = cb.and(where, cb.between(root.get(Pnr_.createTime), sc.getBeginDate(), endDate1));
        } else if (sc.getBeginDate() != null) {
            where = cb.and(where, cb.greaterThan(root.get(Pnr_.createTime), sc.getBeginDate()));
        } else if (sc.getEndDate() != null) {
            Date endDate1 = DateUtil.addDays(sc.getEndDate(), 1);
            where = cb.and(where, cb.lessThan(root.get(Pnr_.createTime), endDate1));
        }


        cq.where(where);
        cq.orderBy(cb.desc(cb.count(root)));

        List<Tuple> results0 = em.createQuery(cq).getResultList();
        for (Tuple result0 : results0) {

            if (results == null) {
                results = new ArrayList<>();
            }

            PnrSummary result = new PnrSummary();

            result.setEtermUsername(result0.get(0, String.class));
            result.setPnrCount(result0.get(1, Long.class).intValue());

            results.add(result);

        }

        logger.debug("statPnrByUsername elapsed time is  {} ms", DateUtil.getPastTime(startTicket));

        if (results == null) {
            results = new ArrayList<>();
        }
        return results;
    }

    /**
     * 搜索ctcmCount = 0 的pnr列表
     *
     * @param enterpriseId      企业ID
     * @param carrier           航司代码
     * @param etermUsername     用户名
     * @param minCreatedMinutes 最少的编码已生成时间
     * @param sc
     * @return
     */
    @Override
    public List<Pnr> searchPnrCtcm(int enterpriseId, String carrier, String etermUsername, int minCreatedMinutes, Integer ctcmCount, CommonSearchCriteria sc) {
        List<Pnr> results = null;


        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<Pnr> cq = cb.createQuery(Pnr.class);

        Root<Pnr> root = cq.from(Pnr.class);
        root.alias("o");

        List<Predicate> predicates = new ArrayList<>();

        Predicate predicate = cb.conjunction();
        predicates.add(predicate);

        predicate = cb.equal(root.get(Pnr_.enterpriseId), enterpriseId);
        predicates.add(predicate);

        if (carrier != null && carrier.length() == 2) {
            predicate = cb.like(root.get(Pnr_.flightNo), carrier + "%");
            predicates.add(predicate);
        }

        if (etermUsername != null && etermUsername.trim().length() > 0) {
            predicate = cb.like(root.get(Pnr_.etermUsername), "%" + etermUsername + "%");
            predicates.add(predicate);
        }

        if (ctcmCount == null) {
            predicate = cb.le(root.get(Pnr_.ctcmCount), 0);
            predicates.add(predicate);
        } else {
            predicate = cb.equal(root.get(Pnr_.ctcmCount), ctcmCount);
            predicate = cb.or(predicate, cb.equal(root.get(Pnr_.ctcmCount), -1));
            predicates.add(predicate);
        }

        predicate = cb.notEqual(root.get(Pnr_.segCount), 0);
        predicates.add(predicate);

        //指定生成时间超过多少分钟的pnr
        Date minCreateTime = DateUtil.addMinutes(DateUtil.getCurDateTime(), -minCreatedMinutes);
        predicate = cb.lessThan(root.get(Pnr_.createTime), minCreateTime);
        predicates.add(predicate);

        if (sc.getExcludeSegStatus() != null && sc.getExcludeSegStatus().length() == 2) {
            //排除指定状态的
            predicate = cb.notEqual(root.get(Pnr_.segStatus), sc.getExcludeSegStatus());
            predicates.add(predicate);
        }


        // count items
        CriteriaQuery<Long> countCq = cb.createQuery(Long.class);
        Root<Pnr> countRoot = countCq.from(Pnr.class);
        countRoot.alias("o");

        countCq.select(cb.count(countRoot)).where(predicates.toArray(new Predicate[predicates.size()]));
        Long count = em.createQuery(countCq).getSingleResult();


        if (count > 0) {
            sc.getPage().setRowCount(count.intValue());

            //search block
            int firstPosition = (sc.getPage().getPageNo() - 1) * sc.getPage().getPageSize();
            cq.select(root).where(predicates.toArray(new Predicate[predicates.size()]));

            List<Order> orderByes = new ArrayList<>();
            orderByes.add(cb.desc(root.get("id")));
            cq.orderBy(orderByes);

            sc.getPage().setRowCount(count.intValue());

            results = em.createQuery(cq)
                    .setFirstResult(firstPosition)
                    .setMaxResults(sc.getPageSize()).getResultList();
        }

        return results == null ? new ArrayList<Pnr>() : results;
    }

    /**
     * 查找缺少联系方式的PNR
     */
    @Override
    public List<Pnr> searchPnrCtct(int enterpriseId, CommonSearchCriteria sc) {
        List<Pnr> results = null;


        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<Pnr> cq = cb.createQuery(Pnr.class);

        Root<Pnr> root = cq.from(Pnr.class);
        root.alias("o");

        List<Predicate> predicates = new ArrayList<>();

        Predicate predicate = cb.conjunction();
        predicates.add(predicate);

        predicate = cb.equal(root.get(Pnr_.enterpriseId), enterpriseId);
        predicates.add(predicate);


        predicate = cb.isNull(root.get(Pnr_.linkphone));
        predicates.add(predicate);

        predicate = cb.or(cb.isNull(root.get(Pnr_.ctctCount)), cb.equal(root.get(Pnr_.ctctCount), 0));
        predicates.add(predicate);

        // count items
        CriteriaQuery<Long> countCq = cb.createQuery(Long.class);
        Root<Pnr> countRoot = countCq.from(Pnr.class);
        countRoot.alias("o");

        countCq.select(cb.count(countRoot)).where(predicates.toArray(new Predicate[predicates.size()]));
        Long count = em.createQuery(countCq).getSingleResult();


        if (count > 0) {
            sc.getPage().setRowCount(count.intValue());

            //search block
            int firstPosition = (sc.getPage().getPageNo() - 1) * sc.getPage().getPageSize();
            cq.select(root).where(predicates.toArray(new Predicate[predicates.size()]));

            List<Order> orderByes = new ArrayList<>();
            orderByes.add(cb.desc(root.get("id")));
            cq.orderBy(orderByes);

            sc.getPage().setRowCount(count.intValue());

            results = em.createQuery(cq)
                    .setFirstResult(firstPosition)
                    .setMaxResults(sc.getPageSize()).getResultList();
        }

        return results == null ? new ArrayList<Pnr>() : results;
    }

    @Override
    public List<PnrMobile> searchPnrMobile(int enterpriseId, CommonSearchCriteria sc) {
        List<PnrMobile> results = null;


        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<PnrMobile> cq = cb.createQuery(PnrMobile.class);

        Root<PnrMobile> root = cq.from(PnrMobile.class);
        root.alias("o");

        List<Predicate> predicates = new ArrayList<>();

        Predicate predicate = cb.conjunction();
        predicates.add(predicate);

        predicate = cb.equal(root.get(PnrMobile_.enterpriseId), enterpriseId);
        predicates.add(predicate);

        // count items
        CriteriaQuery<Long> countCq = cb.createQuery(Long.class);
        Root<PnrMobile> countRoot = countCq.from(PnrMobile.class);
        countRoot.alias("o");

        countCq.select(cb.count(countRoot)).where(predicates.toArray(new Predicate[predicates.size()]));
        Long count = em.createQuery(countCq).getSingleResult();


        if (count > 0) {
            sc.getPage().setRowCount(count.intValue());

            //search block
            int firstPosition = (sc.getPage().getPageNo() - 1) * sc.getPage().getPageSize();
            cq.select(root).where(predicates.toArray(new Predicate[predicates.size()]));

            List<Order> orderByes = new ArrayList<>();
            orderByes.add(cb.desc(root.get("count")));
            orderByes.add(cb.asc(root.get("mobile")));
            cq.orderBy(orderByes);

            sc.getPage().setRowCount(count.intValue());

            results = em.createQuery(cq)
                    .setFirstResult(firstPosition)
                    .setMaxResults(sc.getPageSize()).getResultList();

        }

        return results == null ? new ArrayList<PnrMobile>() : results;
    }

    @Override
    public int createPnrMobile(int enterpriseId, String mobile) {
        PnrMobile pnrMobile = new PnrMobile();
        pnrMobile.setEnterpriseId(enterpriseId);
        pnrMobile.setMobile(mobile);

        em.persist(pnrMobile);

        return pnrMobile.getId();
    }

    @Override
    public int deletePnrMobile(int enterpriseId, int id) {
        return em.createQuery("delete from PnrMobile o where o.id = :id and o.enterpriseId = :enterpriseId")
                .setParameter("id", id)
                .setParameter("enterpriseId", enterpriseId)
                .executeUpdate();
    }

  @Override
  public int createQinfo(Qinfo qinfo) {
      em.persist(qinfo);
      return qinfo.getId();
  }

  @Override
    public Pnr getPnrById(int id) {

        Pnr pnr = em.find(Pnr.class, id);
        if (pnr != null) {
            em.refresh(pnr);
        }
        return pnr;

    }


}
